<?php
include("../header.inc");
require_once("../DataBase.inc");
$Global_mark="";
?>
<h2>Written Exam Results Overview</h2>

<?php
require_once($_SESSION["root_path"]."/exams/exam_queries.inc");

$q = query_exam_centers_having_rooms_and_exam_plan();
$q->join("province", null, array("province.province_id=exam_center.province_id"));
$q->order("province.province_name");
$q->order("exam_center.exam_center_name");
$q->select("province.*");
$q->select("exam_center.*");
$exam_centers = $q->execute();

$subjects = DataBase::query("SELECT * FROM subject WHERE selection_campaign_id=".$_SESSION["campaign_id"]);

echo "<table border=1>";
echo "<tr><th>Province</th><th>Exam Center</th><th>General</th>";
foreach ($subjects as $subject) echo "<th>".$subject["subject_name"]."</th>";
echo "</tr>";
function query_attendees($center) {
	$q = new Query("applicant");
	$q->join_applicant("applicant_exam_room_beta");
	$q->where("applicant_exam_room_beta.exam_center_id=".$center["exam_center_id"]);
	$q->join_applicant("applicant_more_info");
	$q->where("applicant_more_info.applicant_more_info_attendee_exam_status=1");
	return $q;
}
$big_total = array("general"=>0);
$big_nb = array("general"=>0);
foreach ($subjects as $subject) {
	$big_total["s".$subject["subject_id"]] = 0;
	$big_nb["s".$subject["subject_id"]] = 0;
}
foreach ($exam_centers as $center) {
	echo "<tr>";
	echo "<td valign=middle>".$center["province_name"]."</td>";
	echo "<td valign=middle>".$center["exam_center_name"]."</td>";
	$q = query_attendees($center);
	foreach ($subjects as $subject) {
		$q->join_applicant("applicant_score_subject_exam","s".$subject["subject_id"],array("s".$subject["subject_id"].".subject_id=".$subject["subject_id"]));
		$q->select("s".$subject["subject_id"].".score_subject_exam", "ss".$subject["subject_id"]);
	}
	$nb = 0;
	$total = 0;
	$res = $q->execute();
	if ($res !== false)
		foreach ($res as $r) {
			$total_score = 0;
			$has = false;
			$has_empty = false;
			foreach ($subjects as $subject) {
				$score = $r["ss".$subject["subject_id"]];
				if ($score == null) { $has_empty = true; continue; }
				$has = true;
				$total_score += $score;
			}
			if ($has && $has_empty) $nb = -1;
			if ($nb == -1) break;
			if (!$has) continue;
			$nb++;
			$total += $total_score;
		}
	if ($nb > 0) {
		$big_nb["general"] += $nb;
		$big_total["general"] += $total;
		$total /= $nb;
	}
	echo "<td align=center>".($nb == -1 ? "N/A (marks missing)" : $total)."</td>";
	foreach ($subjects as $subject) {
		$q = query_attendees($center);
		$q->join_applicant("applicant_score_subject_exam",null,array("applicant_score_subject_exam.subject_id=".$subject["subject_id"]));
		$nb = 0;
		$total = 0;
		$res = $q->execute();
		if ($res !== false)
			foreach ($res as $r) {
				$score = $r["score_subject_exam"];
				if ($score == null) continue;
				$total += $score;
				$nb++;
			}
		if ($nb > 0) {
			$big_nb["s".$subject["subject_id"]] += $nb;
			$big_total["s".$subject["subject_id"]] += $total;
			$total /= $nb;
		}
		echo "<td align=center>".$total."</td>";
	}
	echo "</tr>";
}
echo "<tr style='background-color:#C0C0C0'><th colspan=2>TOTAL</th><th>";
if ($big_nb["general"] > 0) echo ($big_total["general"]/$big_nb["general"]);
echo "</th>";
foreach ($subjects as $subject) {
	echo "<th>";
	if ($big_nb["s".$subject["subject_id"]] > 0){
            $Global_mark += ($big_total["s".$subject["subject_id"]]/$big_nb["s".$subject["subject_id"]]);
            echo ($big_total["s".$subject["subject_id"]]/$big_nb["s".$subject["subject_id"]]);
        }

	echo "</th>";
}
echo "</tr>";
echo "</table>";
echo $Global_mark;
?>

<?php include '../footer.inc';?>